﻿Imports System.Data.OleDb

Namespace QuanLyKhachHang
    Public Class frmThongKeSanLuongTieuThu
        Private ReportType As Integer = 0
        Private IndexCurrentMonth As Integer = 0
        '* 0 theo ngay - 1 theo thang - 2 theo nam - 3 tu ngay den ngay
        '*
        Private aMaKhachHang As New AutoCompleteStringCollection
#Region "Form Event"
        Private Sub frmThongKeSanLuongTieuThu_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            DisableControl()
            dtpTheoNgay.Enabled = True
            Nam_Load()
            Thang_Load()
            Report_TheoNgay()
            AutoComplete_MaKhachHang()
            TinhThanh_Load()
        End Sub
        Protected Sub TinhThanh_Load()
            Try
                Dim controller As New TinhThanhBLL
                Dim list As New List(Of TinhThanh)
                list = controller.TinhThanh_GetItems()
                cbTinhThanh.DataSource = list
                cbTinhThanh.DisplayMember = "TenTinhThanh"
                cbTinhThanh.ValueMember = "TinhThanhID"
            Catch ex As Exception

            End Try
        End Sub
        Sub DisableControl()
            dtpTheoNgay.Enabled = False
            cbxTheoThang.Enabled = False
            cbxNamTheoThang.Enabled = False
            cbxTheoNam.Enabled = False
            dtpTuNgay.Enabled = False
            dtpDenNgay.Enabled = False
        End Sub

        Private Sub rbtTheoNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNgay.CheckedChanged
            DisableControl()
            dtpTheoNgay.Enabled = True
            ReportType = 0
        End Sub
        Private IsPostBack As Boolean = False
        Private Sub rbtTheoThang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoThang.CheckedChanged
            DisableControl()
            cbxTheoThang.Enabled = True
            cbxNamTheoThang.Enabled = True
            If IsPostBack = False Then
                cbxTheoThang.SelectedIndex = IndexCurrentMonth
            End If
            cbxNamTheoThang.Enabled = True
            If IsPostBack = False Then
                cbxNamTheoThang.SelectedIndex = 0
            End If
            ReportType = 1
            IsPostBack = True
        End Sub
        Private IsPostBack2 As Boolean = False
        Private Sub rbtTheoNam_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNam.CheckedChanged
            DisableControl()
            cbxTheoNam.Enabled = True
            If IsPostBack2 = False Then
                cbxTheoNam.SelectedIndex = 0
            End If
            ReportType = 2
            IsPostBack2 = True
        End Sub

        Private Sub rbtTuNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTuNgay.CheckedChanged
            DisableControl()
            dtpTuNgay.Enabled = True
            dtpDenNgay.Enabled = True
            ReportType = 3
        End Sub
        Private Sub cmdThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThongKe.Click
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
            End Select
        End Sub

        'Function ConvertDateToInt(ByVal Ngay As String)
        '    Dim aNgay As String() = Ngay.Split("/")
        '    'Dim iThang As String
        '    'Dim iNgay As String
        '    Dim Ngay_So As String
        '    'If Integer.Parse(aNgay(1)) <= 9 Then
        '    '    iNgay = "0" & aNgay(1)
        '    'Else
        '    '    iNgay = aNgay(1)
        '    'End If
        '    'If Integer.Parse(aNgay(0)) <= 9 Then
        '    '    iThang = "0" & aNgay(0)
        '    'Else
        '    '    iThang = aNgay(0)
        '    'End If
        '    Ngay_So = aNgay(2) & aNgay(0) & aNgay(1)
        '    Try
        '        Return Integer.Parse(Ngay_So)
        '    Catch ex As Exception
        '        Return 0
        '    End Try
        'End Function

        Function formatDate_VN(ByRef Ngay As String) As Boolean
            Dim aNgay As String() = Ngay.Split("/")
            Ngay = aNgay(1) & "/" & aNgay(0) & "/" & aNgay(2)
        End Function
        Function TinhTongCong(ByVal dtTable As DataTable) As String
            Dim SoTien As Integer = 0
            For i As Integer = 0 To dtTable.Rows.Count - 1
                SoTien = SoTien + ConvertCurencyToInt(dtTable.Rows(i)("ThanhTien"))
            Next
            Return CDec(SoTien.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
        End Function
        Function ConvertCurencyToInt(ByVal Curency As String) As Integer
            Dim retInt As Integer
            If Curency = "" Then
                Return 0
            Else
                Curency = Curency.Split(",")(0).Replace(".", "")
                retInt = Integer.Parse(Curency)
                Return retInt
            End If
        End Function
        Protected Function CreateDataTableTong() As DataTable
            Dim dtTable As DataTable = New DataTable()
            Try
                Dim Tong As DataColumn = New DataColumn("TongCong")
                Tong.DataType = System.Type.GetType("System.String")
                dtTable.Columns.Add(Tong)
                Return dtTable
            Catch ex As Exception
                Throw
            End Try
        End Function
#End Region
#Region "Thống kê - báo cáo"
        '--------Tính tiền doanh thu của khách hàng trên 1 đơn hàng-------------------
        Private Function TinhSanLuongTieuThu(ByVal IDSanPham As Integer, Optional ByVal sSQL As String = "") As Single
            Dim sRet As Single = 0
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dtThanhTien As New DataTable
                Dim query_Tien_HoaDon As String = ""
                Dim adapter As OleDb.OleDbDataAdapter
                Dim TongTienHoaDon As Integer = 0
                If rbtTatCa.Checked Then
                    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
                        query_Tien_HoaDon = String.Format("Select SoLuong From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And IdSanPham={0} " & sSQL, IDSanPham)
                    Else
                        query_Tien_HoaDon = String.Format("Select SoLuong From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And IdSanPham={0} " & sSQL, IDSanPham)
                    End If
                ElseIf rbtChuaDuyet.Checked Then
                    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
                        query_Tien_HoaDon = String.Format("Select SoLuong From ChiTietDonHangBan Where Xoa=0 And TrangThai=N'Chưa duyệt' And LoaiPhi='HangHoa' And IdSanPham={0} " & sSQL, IDSanPham)
                    Else
                        query_Tien_HoaDon = String.Format("Select SoLuong From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And TrangThai=N'Chưa duyệt' And LoaiPhi='HangHoa' And IdSanPham={0} " & sSQL, IDSanPham)
                    End If
                ElseIf rbtDaDuyet.Checked Then
                    query_Tien_HoaDon = String.Format("Select SoLuong From ChiTietDonHangBan Where Xoa=0 And TrangThai=N'Đã duyệt' And LoaiPhi='HangHoa' And IdSanPham={0} " & sSQL, IDSanPham)
                    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
                        query_Tien_HoaDon = String.Format("Select SoLuong From ChiTietDonHangBan Where Xoa=0 And TrangThai=N'Đã duyệt' And LoaiPhi='HangHoa' And IdSanPham={0} " & sSQL, IDSanPham)
                    Else
                        query_Tien_HoaDon = String.Format("Select SoLuong From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And TrangThai=N'Đã duyệt' And LoaiPhi='HangHoa' And IdSanPham={0} " & sSQL, IDSanPham)
                    End If
                End If
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
                adapter.Fill(dtThanhTien)
                For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                    sRet = sRet + Single.Parse((dtThanhTien.Rows(i)("SoLuong")))
                Next
                Return sRet
            Catch ex As Exception
                MessageBox.Show("Có lỗi trong quá trình xử lý.Vui lòng thử lại")
            End Try
        End Function
        Private Function TinhTongDoanhThuKhachHang(ByVal KhachHangId As Integer, ByVal CalOption As Integer)
            Dim sRet As Single = 0
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dtThanhTien As New DataTable
                Dim query_Tien_HoaDon As String
                Dim adapter As OleDb.OleDbDataAdapter
                Dim TongTienHoaDon As Integer = 0
                query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where LoaiPhi='HangHoa' And KhachHangId={0} ", KhachHangId)
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
                adapter.Fill(dtThanhTien)
                For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                    sRet = sRet + ConvertCurencyToInt(dtThanhTien.Rows(i)("ThanhTien"))
                Next
                Return sRet
            Catch ex As Exception
                Throw
            End Try
        End Function
#End Region

#Region "Thống kê theo ngày"
        Protected Function Create_GroupingList() As DataTable
            Dim dtTable As DataTable = New DataTable()
            Try
                Dim IDLoai As DataColumn = New DataColumn("IDLoai")
                IDLoai.DataType = System.Type.GetType("System.String")
                IDLoai.DefaultValue = ""
                dtTable.Columns.Add(IDLoai)

                Dim TenLoai As DataColumn = New DataColumn("TenLoai")
                TenLoai.DataType = System.Type.GetType("System.String")
                TenLoai.DefaultValue = ""
                dtTable.Columns.Add(TenLoai)

                Dim MaSanPham As DataColumn = New DataColumn("MaSanPham")
                MaSanPham.DataType = System.Type.GetType("System.String")
                MaSanPham.DefaultValue = ""
                dtTable.Columns.Add(MaSanPham)

                Dim TenSanPham As DataColumn = New DataColumn("TenSanPham")
                TenSanPham.DataType = System.Type.GetType("System.String")
                TenSanPham.DefaultValue = ""
                dtTable.Columns.Add(TenSanPham)

                Dim DonViTinh As DataColumn = New DataColumn("DonViTinh")
                DonViTinh.DataType = System.Type.GetType("System.String")
                DonViTinh.DefaultValue = ""
                dtTable.Columns.Add(DonViTinh)

                Dim QuyCach As DataColumn = New DataColumn("QuyCach")
                QuyCach.DataType = System.Type.GetType("System.String")
                QuyCach.DefaultValue = ""
                dtTable.Columns.Add(QuyCach)

                Dim SoLuong As DataColumn = New DataColumn("SoLuong")
                SoLuong.DataType = System.Type.GetType("System.String")
                SoLuong.DefaultValue = ""
                dtTable.Columns.Add(SoLuong)

                Dim SoLuongTheoQuyCach As DataColumn = New DataColumn("SoLuongTheoQuyCach")
                SoLuongTheoQuyCach.DataType = System.Type.GetType("System.String")
                SoLuongTheoQuyCach.DefaultValue = ""
                dtTable.Columns.Add(SoLuongTheoQuyCach)

                Return dtTable
            Catch ex As Exception
                Throw
            End Try
        End Function
        REM test lấy tất cả        
#End Region
#Region "Report Theo Tháng"
        Private Sub Report_TheoThang()
            Dim Thang As MonthInfo
            Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
            If Thang Is Nothing Then
                MessageBox.Show("Bạn chưa chọn tháng.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            Dim Nam As YearInfo
            Nam = CType(cbxNamTheoThang.SelectedItem, YearInfo)
            If Nam Is Nothing Then
                MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            Dim TuNgay As Integer
            Dim DenNgay As Integer
            Dim sNgayCuoiThang As String
            Dim sThang As String
            Dim DaysInMonth As Integer = DateTime.DaysInMonth(Nam.TenNam, Thang.TenThang)
            If DaysInMonth <= 9 Then
                sNgayCuoiThang = "0" & DaysInMonth.ToString()
            Else
                sNgayCuoiThang = DaysInMonth.ToString()
            End If
            If Integer.Parse(Thang.TenThang) <= 9 Then
                sThang = "0" & Thang.TenThang
            Else
                sThang = Thang.TenThang
            End If
            TuNgay = Integer.Parse(Nam.TenNam & sThang & "01")
            DenNgay = Integer.Parse(Nam.TenNam & sThang & sNgayCuoiThang)
            ThongKeSanLuongTieuThu(TuNgay, DenNgay)
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'Dim query As String = ""
            'REM Grouping list
            'Dim groupingList As New DataTable
            'groupingList = Create_GroupingList()
            ''title
            'Dim Thang As MonthInfo
            'Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
            'Dim Nam As YearInfo
            'Nam = CType(cbxNamTheoThang.SelectedItem, YearInfo)
            'Dim dtLoaiSanPhamTieuThu As New DataTable
            'If rbtTatCa.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '            " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai"
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "')"
            '    Else
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "')"
            '    End If
            'ElseIf rbtChuaDuyet.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '            " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai"
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "')"
            '    Else
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "')"
            '    End If
            'ElseIf rbtDaDuyet.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '        " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai"
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "')"
            '    Else
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "')"
            '    End If
            'End If
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtLoaiSanPhamTieuThu)
            'frmBusyLoading.Show()
            'frmBusyLoading.progress.Minimum = 0
            'frmBusyLoading.progress.Maximum = dtLoaiSanPhamTieuThu.Rows.Count - 1
            'For i As Integer = 0 To dtLoaiSanPhamTieuThu.Rows.Count - 1
            '    'title
            '    Dim titleRow As DataRow
            '    titleRow = groupingList.NewRow
            '    titleRow.Item("IdLoai") = dtLoaiSanPhamTieuThu.Rows(i)("IdLoai")
            '    titleRow.Item("TenLoai") = dtLoaiSanPhamTieuThu.Rows(i)("TenLoai")
            '    titleRow.Item("MaSanPham") = ""
            '    titleRow.Item("TenSanPham") = ""
            '    titleRow.Item("DonViTinh") = ""
            '    titleRow.Item("QuyCach") = ""
            '    titleRow.Item("SoLuong") = ""
            '    titleRow.Item("SoLuongTheoQuyCach") = ""
            '    groupingList.Rows.Add(titleRow)
            '    'detail
            '    If rbtTatCa.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '                " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And  LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    ElseIf rbtChuaDuyet.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '            " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    ElseIf rbtDaDuyet.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '            " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    End If
            '    Dim dtThongKe As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtThongKe)
            '    Dim sSQL As String
            '    sSQL = " And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' "
            '    Dim TongSoLuong As Integer = 0
            '    Dim TongSoLuongQuyCach As Single = 0
            '    For j As Integer = 0 To dtThongKe.Rows.Count - 1
            '        'Tính Doanh Thu Khách Hàng
            '        'detail row
            '        Dim detailRow As DataRow
            '        detailRow = groupingList.NewRow
            '        detailRow.Item("IdLoai") = ""
            '        detailRow.Item("TenLoai") = ""
            '        detailRow.Item("MaSanPham") = dtThongKe.Rows(j)("MaSanPham")
            '        detailRow.Item("TenSanPham") = dtThongKe.Rows(j)("TenSanPham")
            '        detailRow.Item("DonViTinh") = dtThongKe.Rows(j)("TenDonVi")
            '        'detailRow.Item("QuyCach") = dtThongKe.Rows(j)("TenQuyCach")
            '        detailRow.Item("SoLuong") = TinhSanLuongTieuThu(dtThongKe.Rows(j)("IDSanPham"), sSQL)
            '        TongSoLuong = TongSoLuong + Single.Parse(detailRow.Item("SoLuong"))
            '        detailRow.Item("SoLuongTheoQuyCach") = Single.Parse(dtThongKe.Rows(j)("SoLuongTheoQuyCach")) * Single.Parse(detailRow.Item("SoLuong"))
            '        TongSoLuongQuyCach = TongSoLuongQuyCach + Single.Parse(detailRow.Item("SoLuongTheoQuyCach"))
            '        groupingList.Rows.Add(detailRow)
            '    Next
            '    'update lai so luong
            '    Dim rowUpdate() As DataRow
            '    rowUpdate = groupingList.Select("IDLoai='" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & "'")
            '    If rowUpdate.Length > 0 Then
            '        rowUpdate(0)("SoLuong") = TongSoLuong
            '        rowUpdate(0)("SoLuongTheoQuyCach") = TongSoLuongQuyCach
            '    End If
            '    frmBusyLoading.progress.Value = i
            'Next
            'dbConn.Close()
            'BindGrid(groupingList)
        End Sub
#End Region
        '#Region "Report theo năm"
        Private Sub Report_TheoNam()
            Dim Nam As YearInfo
            Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
            If Nam Is Nothing Then
                MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            Dim TuNgay As Integer = Integer.Parse(Nam.TenNam & "01" & "01")
            Dim DenNgay As Integer = Integer.Parse(Nam.TenNam & "12" & "30")
            ThongKeSanLuongTieuThu(TuNgay, DenNgay)
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'Dim query As String = ""
            'REM Grouping list
            'Dim groupingList As New DataTable
            'groupingList = Create_GroupingList()
            ''title
            'Dim Nam As YearInfo
            'Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
            'Dim dtLoaiSanPhamTieuThu As New DataTable
            'If rbtTatCa.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '            " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai"
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And  Nam='" & Nam.TenNam & "' )"
            '    Else
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And  Nam='" & Nam.TenNam & "' )"
            '    End If
            'ElseIf rbtChuaDuyet.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '        " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai"
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Nam='" & Nam.TenNam & "' )"
            '    Else
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Nam='" & Nam.TenNam & "' )"
            '    End If
            'ElseIf rbtDaDuyet.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '        " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai"
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Nam='" & Nam.TenNam & "' )"
            '    Else
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Nam='" & Nam.TenNam & "' )"
            '    End If
            'End If
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtLoaiSanPhamTieuThu)
            'frmBusyLoading.Show()
            'frmBusyLoading.progress.Minimum = 0
            'frmBusyLoading.progress.Maximum = dtLoaiSanPhamTieuThu.Rows.Count - 1
            'For i As Integer = 0 To dtLoaiSanPhamTieuThu.Rows.Count - 1
            '    'title
            '    Dim titleRow As DataRow
            '    titleRow = groupingList.NewRow
            '    titleRow.Item("IdLoai") = dtLoaiSanPhamTieuThu.Rows(i)("IdLoai")
            '    titleRow.Item("TenLoai") = dtLoaiSanPhamTieuThu.Rows(i)("TenLoai")
            '    titleRow.Item("MaSanPham") = ""
            '    titleRow.Item("TenSanPham") = ""
            '    titleRow.Item("DonViTinh") = ""
            '    titleRow.Item("QuyCach") = ""
            '    titleRow.Item("SoLuong") = ""
            '    groupingList.Rows.Add(titleRow)
            '    'detail
            '    If rbtTatCa.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '                " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And KhachHangId=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    ElseIf rbtChuaDuyet.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '            " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And KhachHangId=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    ElseIf rbtDaDuyet.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '            " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And KhachHangId=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Nam='" & Nam.TenNam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    End If
            '    Dim dtThongKe As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtThongKe)
            '    Dim sSQL As String
            '    sSQL = " And Nam='" & Nam.TenNam & "' "
            '    Dim TongSoLuong As Integer = 0
            '    Dim TongSoLuongQuyCach As Single = 0
            '    For j As Integer = 0 To dtThongKe.Rows.Count - 1
            '        'Tính Doanh Thu Khách Hàng
            '        'detail row
            '        Dim detailRow As DataRow
            '        detailRow = groupingList.NewRow
            '        detailRow.Item("IdLoai") = ""
            '        detailRow.Item("TenLoai") = ""
            '        detailRow.Item("MaSanPham") = dtThongKe.Rows(j)("MaSanPham")
            '        detailRow.Item("TenSanPham") = dtThongKe.Rows(j)("TenSanPham")
            '        detailRow.Item("DonViTinh") = dtThongKe.Rows(j)("TenDonVi")
            '        'detailRow.Item("QuyCach") = dtThongKe.Rows(j)("TenQuyCach")
            '        detailRow.Item("SoLuong") = TinhSanLuongTieuThu(dtThongKe.Rows(j)("IDSanPham"), sSQL)
            '        TongSoLuong = TongSoLuong + Single.Parse(detailRow.Item("SoLuong"))
            '        detailRow.Item("SoLuongTheoQuyCach") = Single.Parse(dtThongKe.Rows(j)("SoLuongTheoQuyCach")) * Single.Parse(detailRow.Item("SoLuong"))
            '        TongSoLuongQuyCach = TongSoLuongQuyCach + Single.Parse(detailRow.Item("SoLuongTheoQuyCach"))
            '        groupingList.Rows.Add(detailRow)
            '    Next
            '    'update lai so luong
            '    Dim rowUpdate() As DataRow
            '    rowUpdate = groupingList.Select("IDLoai='" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & "'")
            '    If rowUpdate.Length > 0 Then
            '        rowUpdate(0)("SoLuong") = TongSoLuong
            '        rowUpdate(0)("SoLuongTheoQuyCach") = TongSoLuongQuyCach
            '    End If
            '    frmBusyLoading.progress.Value = i
            'Next
            'dbConn.Close()
            'BindGrid(groupingList)
        End Sub
#Region "Report từ ngày đến ngày"
        Sub Report_TuNgayDenNgay()
            Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
            Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
            If DenNgay < TuNgay Then
                Dim dlgRes As DialogResult
                dlgRes = MessageBox.Show( _
                          "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                          "Thông báo", _
                    MessageBoxButtons.OK, _
                          MessageBoxIcon.Question)
                If dlgRes = Windows.Forms.DialogResult.OK Then
                    Exit Sub
                End If
            End If
            ThongKeSanLuongTieuThu(TuNgay, DenNgay)
            '2010007004  -->20100704
            'Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
            'Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
            'If DenNgay < TuNgay Then
            '    Dim dlgRes As DialogResult
            '    dlgRes = MessageBox.Show( _
            '              "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
            '              "Thông báo", _
            '        MessageBoxButtons.OK, _
            '              MessageBoxIcon.Question)
            '    If dlgRes = Windows.Forms.DialogResult.OK Then
            '        Exit Sub
            '    End If
            'End If
            ''Dim query As String = "Select * From DonHangBan Where Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " Order by Ngay_So DESC"
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'Dim query As String = ""
            'REM Grouping list
            'Dim groupingList As New DataTable
            'groupingList = Create_GroupingList()
            ''title
            'Dim Nam As YearInfo
            'Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
            'Dim dtLoaiSanPhamTieuThu As New DataTable
            'If rbtTatCa.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '            " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai"
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & ")"
            '    Else
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & ")"
            '    End If
            'ElseIf rbtChuaDuyet.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '        " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai"
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & ")"
            '    Else
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & ")"
            '    End If
            'ElseIf rbtDaDuyet.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '        " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai"
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & ")"
            '    Else
            '        query = query & " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & ")"
            '    End If
            'End If
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtLoaiSanPhamTieuThu)
            'frmBusyLoading.Show()
            'frmBusyLoading.progress.Minimum = 0
            'frmBusyLoading.progress.Maximum = dtLoaiSanPhamTieuThu.Rows.Count - 1
            'For i As Integer = 0 To dtLoaiSanPhamTieuThu.Rows.Count - 1
            '    'title
            '    Dim titleRow As DataRow
            '    titleRow = groupingList.NewRow
            '    titleRow.Item("IdLoai") = dtLoaiSanPhamTieuThu.Rows(i)("IdLoai")
            '    titleRow.Item("TenLoai") = dtLoaiSanPhamTieuThu.Rows(i)("TenLoai")
            '    titleRow.Item("MaSanPham") = ""
            '    titleRow.Item("TenSanPham") = ""
            '    titleRow.Item("DonViTinh") = ""
            '    titleRow.Item("QuyCach") = ""
            '    titleRow.Item("SoLuong") = ""
            '    groupingList.Rows.Add(titleRow)
            '    'detail
            '    If rbtTatCa.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '               " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    ElseIf rbtChuaDuyet.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '           " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    ElseIf rbtDaDuyet.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '           " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    End If
            '    Dim dtThongKe As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtThongKe)
            '    Dim sSQL As String
            '    sSQL = " And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " "
            '    Dim TongSoLuong As Integer = 0
            '    Dim TongSoLuongQuyCach As Single = 0
            '    For j As Integer = 0 To dtThongKe.Rows.Count - 1
            '        'Tính Doanh Thu Khách Hàng
            '        'detail row
            '        Dim detailRow As DataRow
            '        detailRow = groupingList.NewRow
            '        detailRow.Item("IdLoai") = ""
            '        detailRow.Item("TenLoai") = ""
            '        detailRow.Item("MaSanPham") = dtThongKe.Rows(j)("MaSanPham")
            '        detailRow.Item("TenSanPham") = dtThongKe.Rows(j)("TenSanPham")
            '        detailRow.Item("DonViTinh") = dtThongKe.Rows(j)("TenDonVi")
            '        'detailRow.Item("QuyCach") = dtThongKe.Rows(j)("TenQuyCach")
            '        detailRow.Item("SoLuong") = TinhSanLuongTieuThu(dtThongKe.Rows(j)("IDSanPham"), sSQL)
            '        TongSoLuong = TongSoLuong + Single.Parse(detailRow.Item("SoLuong"))
            '        detailRow.Item("SoLuongTheoQuyCach") = Single.Parse(dtThongKe.Rows(j)("SoLuongTheoQuyCach")) * Single.Parse(detailRow.Item("SoLuong"))
            '        TongSoLuongQuyCach = TongSoLuongQuyCach + Single.Parse(detailRow.Item("SoLuongTheoQuyCach"))
            '        groupingList.Rows.Add(detailRow)
            '    Next
            '    'update lai so luong
            '    Dim rowUpdate() As DataRow
            '    rowUpdate = groupingList.Select("IDLoai='" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & "'")
            '    If rowUpdate.Length > 0 Then
            '        rowUpdate(0)("SoLuong") = TongSoLuong
            '        rowUpdate(0)("SoLuongTheoQuyCach") = TongSoLuongQuyCach
            '    End If
            '    frmBusyLoading.progress.Value = i
            'Next
            'dbConn.Close()
            'BindGrid(groupingList)
        End Sub
#End Region
        '#Region "Report theo tên khách hàng"
        '    REM Tìm kiếm tên khách hàng
        '    Private MaKhachHang As String
        '    Sub KhachHang_Search()
        '        Try
        '            Dim dbConn As New OleDb.OleDbConnection
        '            Dim dataAccess As New DataAccess
        '            Dim dsKhachHang As New DataSet
        '            Dim dtKhachHang As New DataTable
        '            Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where TenKhachHang Like N'%" & txtTenKhachHang_Search.Text.Trim & "%'"
        '            Dim adapter As OleDb.OleDbDataAdapter
        '            dataAccess.ConnectDatabase_DBAccess(dbConn)
        '            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        '            adapter.Fill(dsKhachHang)
        '            dbConn.Close()
        '            dtKhachHang = dsKhachHang.Tables(0)
        '            Panel_KhachHang_Search.Visible = True
        '            dgvKhachHang_Search.AutoGenerateColumns = False
        '            dgvKhachHang_Search.DataSource = dtKhachHang
        '        Catch ex As Exception
        '            MessageBox.Show(ex.ToString())
        '        End Try
        '    End Sub

        '    Private Sub txtTenKhachHang_Search_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang_Search.KeyUp
        '        Try
        '            If txtTenKhachHang_Search.Text <> "" Then
        '                KhachHang_Search()
        '            Else
        '                Panel_KhachHang_Search.Visible = False
        '            End If
        '        Catch ex As Exception
        '            MessageBox.Show(ex.ToString())
        '        End Try
        '    End Sub
        '    Private Sub dgvKhachHang_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvKhachHang_Search.CellDoubleClick
        '        Try
        '            txtTenKhachHang_Search.Text = dgvKhachHang_Search.Rows(e.RowIndex).Cells("TenKhachHang_S").Value.ToString()
        '            MaKhachHang = dgvKhachHang_Search.Rows(e.RowIndex).Cells("KhachHangId_ThongKe").Value.ToString()
        '            Panel_KhachHang_Search.Visible = False
        '            cmdThongKe.Focus()
        '        Catch ex As Exception

        '        End Try
        '    End Sub

        '    Private Sub cmdThongKe_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdThongKe.Enter
        '        Try
        '            Panel_KhachHang_Search.Visible = False
        '        Catch ex As Exception

        '        End Try
        '    End Sub
        '    Sub Report_TenKhachHang()
        '        Dim dbConn As New OleDb.OleDbConnection
        '        Dim dataAccess As New DataAccess
        '        'Check Tên Khách Hàng
        '        If IsNotExist(txtTenKhachHang_Search.Text.ToString.Trim) Then
        '            MessageBox.Show("Tên khách hàng bạn nhập chưa có trong danh mục.Vui lòng kiểm tra lại", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
        '            txtTenKhachHang_Search.Focus()
        '        End If
        '        REM end checking 
        '        Dim query As String = "Select * From DonHangBan Where KhachHangId='" & MaKhachHang & "'" & " Order by Ngay_So DESC"
        '        Dim dtThongKe As New DataTable
        '        Dim adapter As OleDb.OleDbDataAdapter
        '        dataAccess.ConnectDatabase_DBAccess(dbConn)
        '        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        '        adapter.Fill(dtThongKe)
        '        dbConn.Close()
        '        dtThongKe.Columns.Add("DoanhThu")
        '        dtThongKe.Columns.Add("STT")
        '        Dim sThanhTien As String
        '        Dim DoanhThu As Single = 0
        '        For i As Integer = 0 To dtThongKe.Rows.Count - 1
        '            'Tính Doanh Thu Khách Hàng
        '            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(i)("IdDonHangBan"))
        '            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
        '            dtThongKe.Rows(i)("DoanhThu") = sThanhTien
        '            dtThongKe.Rows(i)("STT") = i + 1
        '            formatDate2VN(dtThongKe.Rows(i)("NgayBan"))
        '        Next
        '        dgvKhachHang.AutoGenerateColumns = False
        '        dgvKhachHang.DataSource = dtThongKe
        '        If dgvKhachHang.Rows.Count - 1 > 0 Then
        '            dgvKhachHang.Rows(0).Selected = True
        '        End If
        '    End Sub
        '#End Region

#Region "Load data"
        Private Sub Nam_Load()
            Dim currentYear As Integer = Now.Year
            cbxTheoNam.Items.Clear()
            Try
                While (currentYear >= 2011)
                    cbxTheoNam.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                    cbxNamTheoThang.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                    currentYear = currentYear - 1
                End While
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private Sub Thang_Load()
            Dim currentYear As Integer = 1
            cbxTheoThang.Items.Clear()
            Try
                Dim i As Integer = 0
                While (currentYear <= 12)
                    cbxTheoThang.Items.Add(New MonthInfo(currentYear, currentYear.ToString))
                    If currentYear = Now.Month Then
                        IndexCurrentMonth = i
                    End If
                    currentYear = currentYear + 1
                    i = i + 1
                End While
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
#End Region
#Region "Sub Class"
        Private Class YearInfo
            Dim _Nam As Integer
            Dim _TenNam As String
            Public Sub New(ByVal Nam As Integer, ByVal TenNam As String)
                _Nam = Nam
                _TenNam = TenNam
            End Sub
            Public Function Nam() As Integer
                Return _Nam
            End Function
            Public Function TenNam() As String
                Return _TenNam
            End Function
            Public Overrides Function ToString() As String
                Return _TenNam
            End Function
        End Class
        Private Class MonthInfo
            Dim _Thang As Integer
            Dim _TenThang As String
            Public Sub New(ByVal Thang As Integer, ByVal TenThang As String)
                _Thang = Thang
                _TenThang = TenThang
            End Sub
            Public Function Thang() As Integer
                Return _Thang
            End Function
            Public Function TenThang() As String
                Return _TenThang
            End Function
            Public Overrides Function ToString() As String
                Return _TenThang
            End Function
        End Class
#End Region
        Private Sub cmdInThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Try
                Select Case ReportType
                    Case 0
                        frmInThongKeSanLuongTieuThu.ReportType = 0
                        frmInThongKeSanLuongTieuThu.Ngay = dtpTheoNgay.Value.ToShortDateString
                        frmInThongKeSanLuongTieuThu.Show()
                    Case 1
                        frmInThongKeSanLuongTieuThu.ReportType = 1
                        Dim Thang As MonthInfo
                        Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                        frmInThongKeSanLuongTieuThu.Thang = Thang.TenThang
                        frmInThongKeSanLuongTieuThu.Show()
                    Case 2
                        frmInThongKeSanLuongTieuThu.ReportType = 2
                        Dim Nam As YearInfo
                        Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                        frmInThongKeSanLuongTieuThu.Nam = Nam.TenNam
                        frmInThongKeSanLuongTieuThu.Show()
                    Case 3
                        frmInThongKeSanLuongTieuThu.ReportType = 3
                        Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
                        Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
                        If DenNgay < TuNgay Then
                            Dim dlgRes As DialogResult
                            dlgRes = MessageBox.Show( _
                                      "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                                      "Thông báo", _
                                MessageBoxButtons.OK, _
                                      MessageBoxIcon.Question)
                            If dlgRes = Windows.Forms.DialogResult.OK Then
                                Exit Sub
                            End If
                        End If
                        frmInThongKeSanLuongTieuThu.TuNgay = TuNgay
                        frmInThongKeSanLuongTieuThu.DenNgay = DenNgay
                        frmInThongKeSanLuongTieuThu.sTuNgay = dtpTuNgay.Value.ToShortDateString
                        frmInThongKeSanLuongTieuThu.sDenNgay = dtpDenNgay.Value.ToShortDateString
                        frmInThongKeSanLuongTieuThu.Show()
                End Select
            Catch ex As Exception
                ShowError()
            End Try
        End Sub


        Private Sub cemXuatExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cemXuatExcel.Click
            Try
                frmSaveFile.FileType = ".xls"
                ShowModalForm(frmSaveFile, False, False)
                Dim result As Boolean
                result = frmSaveFile.Result
                If result Then
                    Dim Path As String = frmSaveFile.Path
                    Dim FileName As String = frmSaveFile.FileName
                    ExportExcel(Path, FileName & ".xls")
                End If
                frmSaveFile.txtPath.Text = ""
                frmSaveFile.txtFile.Text = ""
            Catch ex As Exception

            End Try
        End Sub
        Private Sub ExportExcel(ByVal Path As String, ByVal FileName As String)
            Try
                Dim SubTitle As String
                If rbtTheoNgay.Checked Then
                    SubTitle = "Ngày " & formatDateVN2String(dtpTheoNgay.Value)
                ElseIf rbtTheoThang.Checked Then
                    Dim Thang As MonthInfo
                    Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                    Dim Nam As YearInfo
                    Nam = CType(cbxNamTheoThang.SelectedItem, YearInfo)
                    SubTitle = " Tháng " & Thang.TenThang & " Năm " & Nam.TenNam
                ElseIf rbtTheoNam.Checked Then
                    Dim Nam As YearInfo
                    Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                    SubTitle = " Năm " & Nam.TenNam
                ElseIf rbtTuNgay.Checked Then
                    SubTitle = " Từ ngày " & formatDateVN2String(dtpTuNgay.Value.ToString.Split(" ")(0)) & " Đến ngày " & formatDateVN2String(dtpDenNgay.Value.ToString.Split(" ")(0))
                End If
                'Dim excelDoc As System.IO.StreamWriter = New System.IO.StreamWriter(FileName)
                Dim startExcelXML As String
                startExcelXML = "<?xml version=""1.0""?>" & vbCrLf & _
                "<?mso-application progid=""Excel.Sheet""?>" & vbCrLf & _
                "<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
                "xmlns:o =""urn:schemas-microsoft-com:office:office""" & vbCrLf & _
                "xmlns:x =""urn:schemas-microsoft-com:office:excel""" & vbCrLf & _
                "xmlns:ss =""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
                 "xmlns:html=""http://www.w3.org/TR/REC-html40""" & ">" & vbCrLf & _
                 "<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office""" & ">" & vbCrLf & _
                  "<LastAuthor>NG0C TRAM</LastAuthor>" & vbCrLf & _
                  "<Created>2011-04-04T13:47:34Z</Created>" & vbCrLf & _
                  "<Version>11.9999</Version>" & vbCrLf & _
                 "</DocumentProperties>" & vbCrLf & _
                 "<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
                  "<WindowHeight>10005</WindowHeight>" & vbCrLf & _
                  "<WindowWidth>10005</WindowWidth>" & vbCrLf & _
                  "<WindowTopX>120</WindowTopX>" & vbCrLf & _
                  "<WindowTopY>135</WindowTopY>" & vbCrLf & _
                  "<ProtectStructure>False</ProtectStructure>" & vbCrLf & _
                  "<ProtectWindows>False</ProtectWindows>" & vbCrLf & _
                 "</ExcelWorkbook>" & vbCrLf & _
                 "<Styles>" & vbCrLf & _
                  "<Style ss:ID=""Default""" & " ss:Name=""Normal""" & ">" & vbCrLf & _
                   "<Alignment ss:Vertical=""Bottom""" & "/>" & vbCrLf & _
                   "<Borders/>" & vbCrLf & _
                   "<Font/>" & vbCrLf & _
                   "<Interior/>" & vbCrLf & _
                   "<NumberFormat/>" & vbCrLf & _
                   "<Protection/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                  "<Style ss:ID=""s22""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "<Font x:Family=""Swiss""" & " ss:Size=""" & "16""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                   "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s24""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "<Font x:Family=""Swiss""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s27""" & ">" & vbCrLf & _
                   "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                   "<Interior ss:Color=""" & "#99CCFF""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s29""" & ">" & vbCrLf & _
                   "<Alignment ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                    "<Style ss:ID=""" & "s30""" & ">" & vbCrLf & _
                    "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                    "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                    "<Interior ss:Color=""" & "#CCFFCC""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                    "</Style>" & vbCrLf & _
                    "<Style ss:ID=""" & "s35""" & ">" & vbCrLf & _
                    "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                    "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                    "<Interior ss:Color=""" & "#CCFFCC""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                    "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s31""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s33""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s32""" & ">" & vbCrLf & _
                  "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                 "</Styles>" & vbCrLf & _
                 "<Worksheet ss:Name=""" & "Sheet1""" & ">" & vbCrLf & _
                  "<Table>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "98.25""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "99.75""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "149.25""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "101.25""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "106.5""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "72""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "50.25""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "69""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "84.75""" & "/>" & vbCrLf & _
                   "<Row ss:AutoFitHeight=""" & "0""" & " ss:Height=""" & "24""" & ">" & vbCrLf & _
                    "<Cell ss:MergeAcross=""" & "7""" & " ss:StyleID=""" & "s22""" & "><Data ss:Type=""" & "String""" & ">BÁO CÁO THỐNG KÊ SẢN LƯỢNG TIÊU THỤ</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                   "</Row>" & vbCrLf & _
                   "<Row ss:Height=""" & "20.25""" & ">" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:MergeAcross=""" & "3""" & " ss:StyleID=""" & "s24""" & "><Data ss:Type=""" & "String""" & ">" & SubTitle & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                   "</Row>" & vbCrLf & _
                   "<Row>" & vbCrLf & _
                    "<Cell ss:Index=""" & "4""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:Index=""" & "9""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                   "</Row>" & vbCrLf & _
                   "<Row ss:Index=""" & "6""" & ">" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">LOẠI SẢN PHẨM</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">MÃ SẢN PHẨM</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">TÊN SẢN PHẨM</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">ĐƠN VỊ TÍNH</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">SỐ LƯỢNG TIÊU THỤ</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">SỐ LƯỢNG (LÍT/KG)</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                   "</Row>"
                'write
                ExportToFile(Path, FileName, startExcelXML)
                Dim contentExcelXML As String = ""
                'contentExcelXML = DevideRowGrid()
                Dim EndRow As Integer = dgvSanLuongTieuThu.RowsCount - 1
                Dim StartRow As Integer = 0
                Dim K As Integer = EndRow \ 100
                Dim SoDu As Integer = EndRow Mod 100
                Dim aK(100) As Integer
                For i As Integer = 1 To 100
                    If K * i <= EndRow Then
                        aK(i) = K * i
                    End If
                Next
                aK(100) = 100 * K + SoDu
                Dim sRet As String = ""
                frmBusyLoading.Show()
                frmBusyLoading.progress.Minimum = 0
                frmBusyLoading.progress.Maximum = 100
                For i As Integer = 0 To 99
                    sRet = ConvertExcel(aK(i) + 1, aK(i + 1))
                    If sRet <> "" Then
                        ExportToFile(Path, FileName, sRet)
                    End If
                    frmBusyLoading.progress.Value = i
                Next
                frmBusyLoading.Close()
                'frmBusyLoading.Show()
                'frmBusyLoading.progress.Minimum = 0
                'frmBusyLoading.progress.Maximum = dgvSanLuongTieuThu.RowsCount
                'For i As Integer = 1 To dgvSanLuongTieuThu.RowsCount - 1
                '    frmBusyLoading.progress.Value = i
                '    If dgvSanLuongTieuThu(i, 0).ColumnSpan = 4 Then
                '        Dim groupt As String = dgvSanLuongTieuThu(i, 0).Value
                '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                '                                "<Cell ss:MergeAcross=""" & "3""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & dgvSanLuongTieuThu(i, 0).Value & "</Data></Cell>" & vbCrLf & _
                '                                "<Cell ss:StyleID=""" & "s35""" & "><Data ss:Type=""" & "String""" & ">" & dgvSanLuongTieuThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                '                                "<Cell ss:StyleID=""" & "s35""" & "><Data ss:Type=""" & "Number""" & ">" & dgvSanLuongTieuThu(i, 5).Value & "</Data></Cell>" & vbCrLf & _
                '                            "</Row>"
                '    Else
                '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                '        "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvSanLuongTieuThu(i, 1).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvSanLuongTieuThu(i, 2).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvSanLuongTieuThu(i, 3).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & dgvSanLuongTieuThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & dgvSanLuongTieuThu(i, 5).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '   "</Row>"
                '    End If

                'Next
                Dim endExcelXML As String
                endExcelXML = "</Table>" & vbCrLf & _
                  "<WorksheetOptions xmlns=""" & "urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
                   "<Print>" & vbCrLf & _
                    "<ValidPrinterInfo/>" & vbCrLf & _
                    "<PaperSizeIndex>0</PaperSizeIndex>" & vbCrLf & _
                    "<HorizontalResolution>600</HorizontalResolution>" & vbCrLf & _
                    "<VerticalResolution>600</VerticalResolution>" & vbCrLf & _
                   "</Print>" & vbCrLf & _
                   "<Selected/>" & vbCrLf & _
                   "<Panes>" & vbCrLf & _
                    "<Pane>" & vbCrLf & _
                     "<Number>3</Number>" & vbCrLf & _
                     "<ActiveRow>1</ActiveRow>" & vbCrLf & _
                     "<ActiveCol>2</ActiveCol>" & vbCrLf & _
                     "<RangeSelection>R2C3:R2C4</RangeSelection>" & vbCrLf & _
                    "</Pane>" & vbCrLf & _
                   "</Panes>" & vbCrLf & _
                   "<ProtectObjects>False</ProtectObjects>" & vbCrLf & _
                   "<ProtectScenarios>False</ProtectScenarios>" & vbCrLf & _
                  "</WorksheetOptions>" & vbCrLf & _
                 "</Worksheet>" & vbCrLf & _
                "</Workbook>"
                ExportToFile(Path, FileName, endExcelXML)
                'excelDoc.Write(startExcelXML & contentExcelXML & endExcelXML)
                'excelDoc.Close()
                'frmBusyLoading.Close()
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub
        Private Function ConvertExcel(ByVal iBegin As Integer, ByVal iEnd As Integer) As String
            Try
                Dim contentExcelXML As String = ""
                For i As Integer = iBegin To iEnd
                    If dgvSanLuongTieuThu(i, 0).ColumnSpan = 4 Then
                        Dim groupt As String = dgvSanLuongTieuThu(i, 0).Value
                        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                                                "<Cell ss:MergeAcross=""" & "3""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & dgvSanLuongTieuThu(i, 0).Value & "</Data></Cell>" & vbCrLf & _
                                                "<Cell ss:StyleID=""" & "s35""" & "><Data ss:Type=""" & "Number""" & ">" & dgvSanLuongTieuThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                                                "<Cell ss:StyleID=""" & "s35""" & "><Data ss:Type=""" & "Number""" & ">" & dgvSanLuongTieuThu(i, 5).Value & "</Data></Cell>" & vbCrLf & _
                                            "</Row>"
                    Else
                        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                        "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvSanLuongTieuThu(i, 1).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvSanLuongTieuThu(i, 2).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvSanLuongTieuThu(i, 3).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & dgvSanLuongTieuThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & dgvSanLuongTieuThu(i, 5).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                   "</Row>"
                    End If
                Next
                Return contentExcelXML
            Catch ex As Exception

            End Try
        End Function
        Protected Sub BindGrid(ByVal dt As DataTable)
            Try
                'bind data
                dgvSanLuongTieuThu.Rows.Clear()
                dgvSanLuongTieuThu.ColumnsCount = 6
                'tạo header 
                dgvSanLuongTieuThu.FixedRows = 1
                dgvSanLuongTieuThu.Rows.Insert(0)
                dgvSanLuongTieuThu(0, 0) = New SourceGrid.Cells.ColumnHeader("Loại sản phẩm")
                dgvSanLuongTieuThu(0, 0).Column.Width = 120
                dgvSanLuongTieuThu(0, 1) = New SourceGrid.Cells.ColumnHeader("Mã sản phẩm")
                dgvSanLuongTieuThu(0, 1).Column.Width = 100
                dgvSanLuongTieuThu(0, 2) = New SourceGrid.Cells.ColumnHeader("Tên sản phẩm")
                dgvSanLuongTieuThu(0, 2).Column.Width = 400
                dgvSanLuongTieuThu(0, 3) = New SourceGrid.Cells.ColumnHeader("Đơn vị tính")
                dgvSanLuongTieuThu(0, 3).Column.Width = 80
                'dgvSanLuongTieuThu(0, 4) = New SourceGrid.Cells.ColumnHeader("Quy cách")
                'dgvSanLuongTieuThu(0, 4).Column.Width = 80
                dgvSanLuongTieuThu(0, 4) = New SourceGrid.Cells.ColumnHeader("Số lượng tiêu thu")
                dgvSanLuongTieuThu(0, 4).Column.Width = 117
                dgvSanLuongTieuThu(0, 5) = New SourceGrid.Cells.ColumnHeader("Số lượng (LÍT/KG)")
                dgvSanLuongTieuThu(0, 5).Column.Width = 117
                'tạo detail
                Dim rowCOLSPAN As New SourceGrid.Cells.Views.Cell
                rowCOLSPAN.BackColor = Color.FromArgb(192, 255, 192)
                Dim cellNumberStyle As New SourceGrid.Cells.Views.Cell
                cellNumberStyle.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
                Dim newFont As New Font(dtpTuNgay.Font, FontStyle.Bold)
                Dim cellBold As New SourceGrid.Cells.Views.Cell
                cellBold.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
                cellBold.Font = newFont
                cellBold.BackColor = Color.FromArgb(192, 255, 192)
                Dim leftStyle As New SourceGrid.Cells.Views.Cell
                leftStyle.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleLeft
                For i As Integer = 0 To dt.Rows.Count - 1
                    dgvSanLuongTieuThu.Rows.Insert(i + 1)
                    If dt.Rows(i)("TenLoai").ToString <> "" Then
                        dgvSanLuongTieuThu(i + 1, 0) = New SourceGrid.Cells.Cell(dt.Rows(i)("TenLoai").ToString)
                        dgvSanLuongTieuThu(i + 1, 0).ColumnSpan = 4
                        dgvSanLuongTieuThu(i + 1, 0).View = rowCOLSPAN
                        dgvSanLuongTieuThu(i + 1, 4) = New SourceGrid.Cells.Cell(dt.Rows(i)("SoLuong").ToString)
                        dgvSanLuongTieuThu(i + 1, 4).View = cellBold
                        dgvSanLuongTieuThu(i + 1, 5) = New SourceGrid.Cells.Cell(dt.Rows(i)("SoLuongTheoQuyCach").ToString)
                        dgvSanLuongTieuThu(i + 1, 5).View = cellBold
                    Else
                        dgvSanLuongTieuThu(i + 1, 0) = New SourceGrid.Cells.Cell()
                        dgvSanLuongTieuThu(i + 1, 1) = New SourceGrid.Cells.Cell(dt.Rows(i)("MaSanPham").ToString)
                        dgvSanLuongTieuThu(i + 1, 1).View = leftStyle
                        dgvSanLuongTieuThu(i + 1, 2) = New SourceGrid.Cells.Cell(dt.Rows(i)("TenSanPham").ToString)
                        dgvSanLuongTieuThu(i + 1, 2).View = leftStyle
                        dgvSanLuongTieuThu(i + 1, 3) = New SourceGrid.Cells.Cell(dt.Rows(i)("DonViTinh").ToString)
                        dgvSanLuongTieuThu(i + 1, 3).View.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleCenter
                        'dgvSanLuongTieuThu(i + 1, 4) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("QuyCach").ToString)
                        dgvSanLuongTieuThu(i + 1, 4) = New SourceGrid.Cells.Cell(dt.Rows(i)("SoLuong").ToString)
                        dgvSanLuongTieuThu(i + 1, 4).View = cellNumberStyle
                        dgvSanLuongTieuThu(i + 1, 5) = New SourceGrid.Cells.Cell(dt.Rows(i)("SoLuongTheoQuyCach").ToString)
                        dgvSanLuongTieuThu(i + 1, 5).View = cellNumberStyle
                    End If
                Next
                frmBusyLoading.Close()
            Catch ex As Exception

            End Try
        End Sub
#Region "Tìm kiếm khách hàng"
        Private Sub txtTenKhachHang_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyDown
            Try
                If e.KeyCode = Keys.Enter Then
                    lblKhachHangId.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                    '---------Lấy Thống Tin Khách Hàng----------
                    Dim dbConn As New OleDb.OleDbConnection
                    Dim dataAccess As New DataAccess
                    Dim dsTinhThanh As New DataSet
                    Dim dtTinhThanh As New DataTable
                    Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangId.Text
                    Dim adapter As OleDb.OleDbDataAdapter
                    dataAccess.ConnectDatabase_DBAccess(dbConn)
                    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                    adapter.Fill(dsTinhThanh)

                    dtTinhThanh = dsTinhThanh.Tables(0)
                    '
                    txtMaKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("CodeKhachHang")), "", dtTinhThanh.Rows(0)("CodeKhachHang"))
                    txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                    Panel_KhachHang.Visible = False
                    cmdThongKe.Focus()
                    dbConn.Close()
                ElseIf e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then

                End If
            Catch ex As Exception

            End Try
        End Sub
        Private Sub txtTenKhachHang_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyUp
            Try
                If txtTenKhachHang.Text <> "" Then
                    KhachHang_Quick_Search()
                    If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                        dgv_KhachHang_QuickSearch.Focus()
                        Panel_KhachHang.Visible = True
                    End If
                Else
                    Panel_KhachHang.Visible = False
                    lblKhachHangId.Text = "0"
                    txtMaKhachHang.Text = ""
                End If
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub
        Private Sub txtTenKhachHang_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTenKhachHang.Leave
            Try
                Panel_KhachHang.Visible = False
            Catch ex As Exception

            End Try
        End Sub
        Sub KhachHang_Quick_Search()
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsKhachHang As New DataSet
                Dim dtKhachHang As New DataTable
                Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where TenKhachHang Like N'%" & txtTenKhachHang.Text.Trim & "%'"
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dsKhachHang)
                dbConn.Close()
                dtKhachHang = dsKhachHang.Tables(0)
                Panel_KhachHang.Visible = True
                dgv_KhachHang_QuickSearch.AutoGenerateColumns = False
                dgv_KhachHang_QuickSearch.DataSource = dtKhachHang
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub
        Public Sub AutoComplete_MaKhachHang()
            Try
                'Auto Complete 
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dtCustomerInfo As New DataTable
                Dim query As String = "Select CodeKhachHang From DanhMucKhachHang Where Xoa=0"
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtCustomerInfo)
                dbConn.Close()
                aMaKhachHang = New AutoCompleteStringCollection
                For i As Integer = 0 To dtCustomerInfo.Rows.Count - 1
                    aMaKhachHang.Add(dtCustomerInfo.Rows(i)("CodeKhachHang"))
                Next
                txtMaKhachHang.AutoCompleteMode = AutoCompleteMode.SuggestAppend
                txtMaKhachHang.AutoCompleteSource = AutoCompleteSource.CustomSource
                txtMaKhachHang.AutoCompleteCustomSource = aMaKhachHang
            Catch ex As Exception

            End Try
        End Sub
        Private Sub txtMaKhachHang_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtMaKhachHang.KeyDown
            Try
                If e.KeyCode = Keys.Enter Then
                    'Truyen MaKhachHang De Lay Thong Tin
                    Dim dtKhachHangInfo As New DataTable
                    Dim dbConn As New OleDb.OleDbConnection
                    Dim dataAccess As New DataAccess
                    Dim adapter As OleDb.OleDbDataAdapter
                    Dim dbCommand As OleDb.OleDbCommand
                    dataAccess.ConnectDatabase_DBAccess(dbConn)
                    dbCommand = New OleDbCommand("spKhachHang_Item", dbConn)
                    dbCommand.CommandType = CommandType.StoredProcedure
                    dbCommand.Parameters.Add("@KhachHangId", OleDbType.Integer).Value = 0
                    dbCommand.Parameters.Add("@CodeKhachHang", OleDbType.VarWChar).Value = txtMaKhachHang.Text.Trim
                    dbCommand.Parameters.Add("@Type", OleDbType.Integer).Value = 1
                    adapter = New OleDb.OleDbDataAdapter(dbCommand)
                    adapter.Fill(dtKhachHangInfo)
                    dbConn.Close()
                    If dtKhachHangInfo.Rows.Count > 0 Then
                        lblKhachHangId.Text = dtKhachHangInfo.Rows(0)("KhachHangId")
                        txtMaKhachHang.Text = IIf(IsDBNull(dtKhachHangInfo.Rows(0)("CodeKhachHang")), "", dtKhachHangInfo.Rows(0)("CodeKhachHang"))
                        txtTenKhachHang.Text = IIf(IsDBNull(dtKhachHangInfo.Rows(0)("TenKhachHang")), "", dtKhachHangInfo.Rows(0)("TenKhachHang"))
                    Else
                        txtMaKhachHang.Focus()
                    End If
                End If
            Catch ex As Exception

            End Try
        End Sub
        Private Sub dgv_KhachHang_QuickSearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgv_KhachHang_QuickSearch.KeyDown
            Try
                Select Case e.KeyCode
                    Case Keys.Down
                        Panel_KhachHang.Visible = True
                    Case Keys.Up
                        Panel_KhachHang.Visible = True
                    Case Keys.Enter
                        lblKhachHangId.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                        '---------Lấy Thống Tin Khách Hàng----------
                        Dim dbConn As New OleDb.OleDbConnection
                        Dim dataAccess As New DataAccess
                        Dim dsTinhThanh As New DataSet
                        Dim dtTinhThanh As New DataTable
                        Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangId.Text
                        Dim adapter As OleDb.OleDbDataAdapter
                        dataAccess.ConnectDatabase_DBAccess(dbConn)
                        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                        adapter.Fill(dsTinhThanh)

                        dtTinhThanh = dsTinhThanh.Tables(0)
                        '
                        txtMaKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("CodeKhachHang")), "", dtTinhThanh.Rows(0)("CodeKhachHang"))
                        txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                        '
                        Panel_KhachHang.Visible = False
                        dbConn.Close()
                    Case Else
                        txtTenKhachHang.Focus()
                End Select
            Catch ex As Exception

            End Try
        End Sub
#End Region

        Private Sub cmdTinhThanh_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdTinhThanh.CheckedChanged
            Try
                cbTinhThanh.Enabled = cmdTinhThanh.Checked
            Catch ex As Exception

            End Try
        End Sub
        Sub Report_TheoNgay()
            Dim TuNgay As Integer = ConvertDateToInt(dtpTheoNgay.Value.ToString.Split(" ")(0))
            Dim DenNgay As Integer = ConvertDateToInt(dtpTheoNgay.Value.ToString.Split(" ")(0))
            ThongKeSanLuongTieuThu(TuNgay, DenNgay)
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            ''Dim NgayBan As String = dtpTheoNgay.Value.ToShortDateString
            'Dim query As String = "" '"Select * From DonHangBan Order by Ngay_So DESC" REM Where NgayBan='" & NgayBan & "'" & " Order by Ngay_So DESC"
            'REM Grouping list
            'Dim groupingList As New DataTable
            'groupingList = Create_GroupingList()
            ''title
            'Dim dtLoaiSanPhamTieuThu As New DataTable
            'If rbtTatCa.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '            " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai" & _
            '            " Where IDSanPham "
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' )"
            '    Else
            '        query = query & " In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai<>N'Đã hủy' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' )"
            '    End If
            'ElseIf rbtChuaDuyet.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '            " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai" & _
            '            " Where IDSanPham "
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' )"
            '    Else
            '        query = query & " In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Chưa duyệt' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' )"
            '    End If
            'ElseIf rbtDaDuyet.Checked Then
            '    query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
            '        " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai" & _
            '        " Where IDSanPham "
            '    If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '        query = query & " In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' )"
            '    Else
            '        query = query & " In ( Select IDSanPham From ChiTietDonHangBan Where Xoa=0 And KhachHangID=" & lblKhachHangId.Text & " And LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' )"
            '    End If
            'End If
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtLoaiSanPhamTieuThu)
            'frmBusyLoading.Show()
            'frmBusyLoading.progress.Minimum = 0
            'frmBusyLoading.progress.Maximum = dtLoaiSanPhamTieuThu.Rows.Count
            'For i As Integer = 0 To dtLoaiSanPhamTieuThu.Rows.Count - 1
            '    frmBusyLoading.progress.Value = i
            '    'title
            '    Dim titleRow As DataRow
            '    titleRow = groupingList.NewRow
            '    titleRow.Item("IdLoai") = dtLoaiSanPhamTieuThu.Rows(i)("IdLoai")
            '    titleRow.Item("TenLoai") = dtLoaiSanPhamTieuThu.Rows(i)("TenLoai")
            '    titleRow.Item("MaSanPham") = ""
            '    titleRow.Item("TenSanPham") = ""
            '    titleRow.Item("DonViTinh") = ""
            '    titleRow.Item("QuyCach") = ""
            '    titleRow.Item("SoLuong") = ""
            '    groupingList.Rows.Add(titleRow)
            '    'detail
            '    If rbtTatCa.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '                " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And CT.LoaiPhi='HangHoa' And CT.TrangThai<>N'Đã hủy' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And CT.KhachHangID=" & lblKhachHangId.Text & " And CT.LoaiPhi='HangHoa' And CT.TrangThai<>N'Đã hủy' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    ElseIf rbtChuaDuyet.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '                " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And CT.LoaiPhi='HangHoa' And CT.TrangThai=N'Chưa duyệt' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And CT.KhachHangID=" & lblKhachHangId.Text & " And CT.LoaiPhi='HangHoa' And CT.TrangThai=N'Chưa duyệt' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    ElseIf rbtDaDuyet.Checked Then
            '        query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,DVT.TenDonViTinh As TenDonVi,DVT.SoLuong AS SoLuongTheoQuyCach From ChiTietDonHangBan as CT" & _
            '                " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham Left Outer Join DVTSanPham as DVT On Sp.DVTSanPhamId=DVT.DVTSanPhamId "
            '        If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
            '            query = query & " Where CT.Xoa=0 And CT.LoaiPhi='HangHoa' And CT.TrangThai=N'Đã duyệt' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        Else
            '            query = query & " Where CT.Xoa=0 And CT.KhachHangID=" & lblKhachHangId.Text & " And CT.LoaiPhi='HangHoa' And CT.TrangThai=N'Đã duyệt' And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
            '        End If
            '    End If
            '    Dim dtThongKe As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtThongKe)
            '    Dim sSQL As String = " And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "'"
            '    Dim TongSoLuong As Integer = 0
            '    Dim TongSoLuongQuyCach As Single = 0
            '    For j As Integer = 0 To dtThongKe.Rows.Count - 1
            '        'Tính Doanh Thu Khách Hàng
            '        'detail row
            '        Dim detailRow As DataRow
            '        detailRow = groupingList.NewRow
            '        detailRow.Item("IdLoai") = ""
            '        detailRow.Item("TenLoai") = ""
            '        detailRow.Item("MaSanPham") = dtThongKe.Rows(j)("MaSanPham")
            '        detailRow.Item("TenSanPham") = dtThongKe.Rows(j)("TenSanPham")
            '        detailRow.Item("DonViTinh") = dtThongKe.Rows(j)("TenDonVi")
            '        'detailRow.Item("QuyCach") = dtThongKe.Rows(j)("TenQuyCach")

            '        detailRow.Item("SoLuong") = TinhSanLuongTieuThu(dtThongKe.Rows(j)("IDSanPham"), sSQL)
            '        TongSoLuong = TongSoLuong + Single.Parse(detailRow.Item("SoLuong"))
            '        detailRow.Item("SoLuongTheoQuyCach") = Single.Parse(dtThongKe.Rows(j)("SoLuongTheoQuyCach")) * Single.Parse(detailRow.Item("SoLuong"))
            '        TongSoLuongQuyCach = TongSoLuongQuyCach + Single.Parse(detailRow.Item("SoLuongTheoQuyCach"))
            '        groupingList.Rows.Add(detailRow)
            '    Next
            '    'update lai so luong
            '    Dim rowUpdate() As DataRow
            '    rowUpdate = groupingList.Select("IDLoai='" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & "'")
            '    If rowUpdate.Length > 0 Then
            '        rowUpdate(0)("SoLuong") = TongSoLuong
            '        rowUpdate(0)("SoLuongTheoQuyCach") = TongSoLuongQuyCach
            '    End If
            'Next
            'dbConn.Close()
            'BindGrid(groupingList)
        End Sub
#Region "Nâng cấp lên .Net SQL Provider"
        Sub ThongKeSanLuongTieuThu(ByVal TuNgay As Integer, ByVal DenNgay As Integer)
            Dim groupingList As New DataTable
            groupingList = Create_GroupingList()
            Dim TrangThai As String
            Dim Type As Integer
            If cbxMuaTrucTiep.Checked Then
                Type = 0
            Else
                Type = 1
            End If
            If rbtTatCa.Checked Then
                TrangThai = "Tất cả"
            ElseIf rbtChuaDuyet.Checked Then
                TrangThai = "Chưa duyệt"
            ElseIf rbtDaDuyet.Checked Then
                TrangThai = "Đã duyệt"
            End If
            Dim KhachHangID As Integer
            If lblKhachHangId.Text = "" Or lblKhachHangId.Text = "0" Then
                KhachHangID = 0
            Else
                KhachHangID = lblKhachHangId.Text
            End If
            Dim MaTinhThanh As Integer
            If cmdTinhThanh.Checked Then
                MaTinhThanh = cbTinhThanh.SelectedValue
            Else
                MaTinhThanh = 0
            End If
            'title
            Dim dtLoaiSanPhamTieuThu As New List(Of LoaiSanPham)
            Dim controller As New ThongKeSanLuongBLL
            dtLoaiSanPhamTieuThu = controller.ThongKeSanLuong_LoaiSanPham(TrangThai, TuNgay, DenNgay, KhachHangID, MaTinhThanh, Type)
            frmBusyLoading.Show()
            frmBusyLoading.progress.Minimum = 0
            frmBusyLoading.progress.Maximum = dtLoaiSanPhamTieuThu.Count
            For i As Integer = 0 To dtLoaiSanPhamTieuThu.Count - 1
                frmBusyLoading.progress.Value = i
                'title
                Dim titleRow As DataRow
                titleRow = groupingList.NewRow
                titleRow.Item("IdLoai") = dtLoaiSanPhamTieuThu(i).IDLoai
                titleRow.Item("TenLoai") = dtLoaiSanPhamTieuThu(i).TenLoai
                titleRow.Item("MaSanPham") = ""
                titleRow.Item("TenSanPham") = ""
                titleRow.Item("DonViTinh") = ""
                titleRow.Item("QuyCach") = ""
                titleRow.Item("SoLuong") = ""
                groupingList.Rows.Add(titleRow)
                'detail
                Dim dtThongKe As New List(Of SanPham)
                dtThongKe = controller.ThongKeSanLuong_SanPham(TrangThai, TuNgay, DenNgay, dtLoaiSanPhamTieuThu(i).IDLoai, KhachHangID, MaTinhThanh, Type)
                Dim sSQL As String = " And Ngay='" & dtpTheoNgay.Value.ToShortDateString & "'"
                Dim TongSoLuong As Integer = 0
                Dim TongSoLuongQuyCach As Single = 0
                Dim SoLuongTheoQuyCach As Single = 0
                For j As Integer = 0 To dtThongKe.Count - 1
                    'Tính Doanh Thu Khách Hàng
                    'detail row
                    Dim detailRow As DataRow
                    detailRow = groupingList.NewRow
                    detailRow.Item("IdLoai") = ""
                    detailRow.Item("TenLoai") = ""
                    detailRow.Item("MaSanPham") = dtThongKe(j).MaSanPham
                    detailRow.Item("TenSanPham") = dtThongKe(j).TenSanPham
                    detailRow.Item("DonViTinh") = dtThongKe(j).TenDonVi
                    detailRow.Item("SoLuong") = controller.ThongKeSanLuong_TieuThu(TrangThai, TuNgay, DenNgay, dtThongKe(j).IDSanPham, KhachHangID, MaTinhThanh, Type)
                    TongSoLuong = TongSoLuong + Single.Parse(detailRow.Item("SoLuong"))
                    SoLuongTheoQuyCach = Single.Parse(dtThongKe(j).SoLuongTheoQuyCach) * Single.Parse(detailRow.Item("SoLuong"))
                    SoLuongTheoQuyCach = LamTron(SoLuongTheoQuyCach, 2)
                    detailRow.Item("SoLuongTheoQuyCach") = SoLuongTheoQuyCach
                    TongSoLuongQuyCach = TongSoLuongQuyCach + Single.Parse(detailRow.Item("SoLuongTheoQuyCach"))
                    groupingList.Rows.Add(detailRow)
                Next
                'update lai so luong
                Dim rowUpdate() As DataRow
                rowUpdate = groupingList.Select("IDLoai='" & dtLoaiSanPhamTieuThu(i).IDLoai & "'")
                If rowUpdate.Length > 0 Then
                    rowUpdate(0)("SoLuong") = TongSoLuong
                    rowUpdate(0)("SoLuongTheoQuyCach") = TongSoLuongQuyCach
                End If
            Next
            BindGrid(groupingList)
        End Sub
#End Region
    End Class
End Namespace